import pymysql

# 连接数据库
db = pymysql.connect(host='wlff.site', user='remoteroot', port=3307, password='213471284', database='Book_management')

#查询图书信息列表
def Inquire_book():

    # 使用cursor()方法获取操作游标
    cursor = db.cursor()

    # 查询图书清单，展示出图书列表和可借阅数量
    Inquire_book_list = "SELECT * FROM book_info"

    # 执行sql语句
    cursor.execute(Inquire_book_list)

    data_list = cursor.fetchall()
    result = []
    for row in data_list:
        book_name = row[3]
        book_num = row[2]
        result.append({"图书名称": book_name, "可借阅数量": book_num})
    return result

Inquire_book()

#添加书籍
def add_book(book_num,book_name):
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()

    data = ("SELECT * FROM book_info where book_name = %s" %book_name)

    if cursor.execute(data) == 1:
        return "书籍已存在，请勿重复添加"
    add_book_one = ("INSERT INTO book_info(book_num,book_surplus,book_name) values(%d,%d,%s)"%(book_num,book_num,book_name))

    if cursor.execute(add_book_one) == 1 :
        db.commit()
        return "书籍添加成功"
    else:
        return "书籍添加失败"
        db.rollback()
    db.close()

def delete_book(book_id):
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()

    delete_book_one = ("DELETE FROM book_info where book_id = %d" %book_id)

    if cursor.execute(delete_book_one) == 1 :
        db.commit()
        return "书籍删除成功"
    else:
        return "书籍不存在，删除失败"
        db.rollback()
    db.close()

# def user_borrow(name,passwd,book_id,book_num,user_id):
#     # 使用cursor()方法获取操作游标
#     cursor = db.cursor()
#
#     user_login = ("SELECT * FROM user where account = '%s' AND password = '%s' "%(name,passwd))
#
#     if not cursor.execute(user_login) == 1 :
#         raise Exception("用户名或密码输入错误")
#     borrow_book = ("SELECT * FROM book_info where book_id = %d" %book_id)
#     if not cursor.execute(borrow_book) == 1 :
#         raise Exception("书籍不存在")
#     data = cursor.fetchone()
#     cursor.execute("UPDATE book_info SET book_surplus = %d" %(data[1]-book_num))
#     cursor.execute("INSERT INTO book_borrow SET(book_id,book_num,user_id,borrow_time) VALUES(%d,%d,%d,%s)"%(book_id,book_num,user_id))




